package com.luomu.base.control.cookBook.excel.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author fxz
 * @description easyExcel来导出xlsx的工具类，拆分sheet
 */
public class EasyExcelAndSheetUtils {

    private static final Logger log = LoggerFactory.getLogger(EasyExcelAndSheetUtils.class);

    //拆分数据最大值
    private static final int MAXROWS = 50000;

    /**
     * 导出
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: " + data.size() + "条。");
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(defaultStyles()).doWrite(data);
        log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
    }

    /** 根据实体BO导出excel
     * @param response 响应对象
     * @param data     查询结果
     * @param fileName 导出文件名称
     * @param clazz    映射实体class类
     * @param <T>      查询结果类型
     * @throws Exception
     * @author QiuYu
     * @createDate 2025-03-07
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class clazz) throws Exception {
        OutputStream out = getOutputStream(fileName, response);
        try {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: " + data.size() + "条。");

            List<List<T>> lists = splitList(data, MAXROWS); // 分割的集合

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(defaultStyles());
            ExcelWriter excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); i++) {
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i - 1), writeSheet);
            }
            out.flush();
            excelWriter.finish();
            out.close();
            log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
        } catch (Exception e) {
            log.error("导出入库单异常",e);
        }finally {
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception ex){
                log.error("导出入库单-关闭流异常",ex);
            }
        }

    }

    /** 根据实体BO导出excel 附带excel样式
     * @param response 响应对象
     * @param data     查询结果
     * @param fileName 导出文件名称
     * @param clazz    映射实体class类
     * @param <T>      查询结果类型
     * @throws Exception
     * @author QiuYu
     * @createDate 2025-03-07
     */
    public static <T> void writeExcelOfStyle(HttpServletResponse response, List<T> data, String fileName, Class clazz,HorizontalCellStyleStrategy Style) throws Exception {
        OutputStream out = getOutputStream(fileName, response);

        try {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: " + data.size() + "条。");

            List<List<T>> lists = splitList(data, MAXROWS); // 分割的集合

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(Style);
            ExcelWriter excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); i++) {
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i - 1), writeSheet);
            }
            out.flush();
            excelWriter.finish();
            out.close();
            log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
        } catch (Exception e) {
            log.error("导出入库单异常",e);
        }finally {
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception ex){
                log.error("导出入库单-关闭流异常",ex);
            }
        }
    }

    /**
     * 导出excel模板文件
     * @param response 响应对象
     * @param fileName 文件名称
     * @param resource 模板文件
     * @param <T> 数据类型
     * @throws Exception
     */
    public static <T> void writeExcelTemplate(HttpServletResponse response, String fileName, InputStream resource) throws Exception {
        OutputStream out = getOutputStream(fileName, response);
        try {
            long exportStartTime = System.currentTimeMillis();
            ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(resource).excelType(ExcelTypeEnum.XLSX).build();
            out.flush();
            excelWriter.finish();
            log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
        } catch (Exception e) {
            log.error("导出入库单异常",e);
        }finally {
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception ex){
                log.error("导出入库单-关闭流异常",ex);
            }
        }
    }

    /**
     * 通过模板导出数据
     * @param response 响应对象
     * @param data excel数据
     * @param fileName 表名称
     * @param resource 模板文件
     * @param <T> 数据类型
     * @throws Exception
     */
    public static <T> void writeExcelByTemplate(HttpServletResponse response, List<T> data, String fileName, InputStream resource) throws Exception {
        OutputStream out = getOutputStream(fileName, response);
        try {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: " + data.size() + "条。");

            List<List<T>> lists = splitList(data, MAXROWS); // 分割的集合

            ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(resource).excelType(ExcelTypeEnum.XLSX).build();

            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); i++) {
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.fill(lists.get(i - 1), writeSheet);
            }
            out.flush();
            excelWriter.finish();
            out.close();
            log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");

        } catch (Exception e) {
            log.error("导出入库单异常",e);
        }finally {
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception ex){
                log.error("导出入库单-关闭流异常",ex);
            }
        }
    }

    /**
     * 多sheet导出
     * @param response 响应
     * @param map 集合
     * @param fileName 文件名
     * @param sheetNames sheet名
     * @throws Exception
     */
    public static void writeMore(HttpServletResponse response, LinkedHashMap<Class, List> map, String fileName, String[] sheetNames) throws Exception {
        OutputStream out = getOutputStream(fileName, response);
        try {
            long exportStartTime = System.currentTimeMillis();
            ExcelWriter excelWriter = EasyExcel.write(out).registerWriteHandler(defaultStyles()).build();
            int i = 0;
            for (Class c : map.keySet()) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetNames[i]).head(c).build();
                i++;
                excelWriter.write(map.get(c), writeSheet);
            }
            //这一步很关键，不然文件会损坏
            excelWriter.finish();
            out.flush();
            out.close();
            log.info("报表导出结束时间:" + new Date() + ";写入耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
        } catch (Exception e) {
            log.error("导出入库单异常",e);
        }finally {
            try{
                if(out != null){
                    out.close();
                }
            }catch (Exception ex){
                log.error("导出入库单-关闭流异常",ex);
            }
        }
    }

    /**
     * 配置请求头 获取输出流
     * @param fileName 文件名
     * @param response 响应对象
     * @return
     * @throws Exception
     */
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel"); // .xls
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");//开放权限，让前端获取到

        return response.getOutputStream();
    }

    /**
     * @param list 待切割集合
     * @param len  集合按照多大size来切割
     * @param <T>
     * @return
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int size = list.size();
        int count = (size + len - 1) / len;

        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
            result.add(subList);
        }
        return result;
    }


    /**
     * @param source 源集合
     * @param n      分成n个集合
     * @param <T>    集合类型
     * @return
     * @description 集合平均分组
     */
    public static <T> List<List<T>> groupList(List<T> source, int n) {
        if (source == null || source.size() == 0 || n < 1) {
            return null;
        }
        if (source.size() < n) {
            return Arrays.asList(source);
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int number = source.size() / n;
        int remaider = source.size() % n;
        int offset = 0; // 偏移量，每有一个余数分配，就要往右偏移一位
        for (int i = 0; i < n; i++) {
            List<T> list1 = null;
            if (remaider > 0) {
                list1 = source.subList(i * number + offset, (i + 1) * number + offset + 1);
                remaider--;
                offset++;
            } else {
                list1 = source.subList(i * number + offset, (i + 1) * number + offset);
            }
            result.add(list1);
        }

        return result;
    }


    /**
     * 获取默认表头内容的样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 常用样式
     * @return
     */
    public static HorizontalCellStyleStrategy defaultStyles() {
        //TODO 默认样式
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置浅灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格上下左右边框为细边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
        //创建字体对象
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setBold(false);//不加粗
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

    /**
     * 白底表头样式
     * @return
     */
    public static HorizontalCellStyleStrategy whiteHeadStyles() {
        //TODO 默认样式
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.index);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格上下左右边框为细边框
//        contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
//        contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
//        contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
//        contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
        //创建字体对象
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setBold(false);//不加粗
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

}
